using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using ApplicationSupport;
using Cubes.Adapters;

namespace Cubes.Data
{
	/// <summary>
	/// Data Access tools
	/// </summary>
	public class DataTools
	{
		private string currentDatabase;
		private BaseAdapter adapter;

		
		// Support for built-in adapters
		private BaseAdapter GetAdapter(string database)
		{
			string provider = System.Configuration.ConfigurationManager.ConnectionStrings[database].ProviderName;
			switch (provider)
			{
				case "System.Data.SqlClient":
					return new SqlServer(database);
				default:
					throw new ArgumentException("Invalid database provider \"" + provider + "\"");
			}
		}
		public static DataTools Default
		{
			get { return new DataTools("Default"); }
		}

		#region Constructors

		public DataTools(string database)
		{
			this.adapter = this.GetAdapter(database);
		}

		#endregion

		#region GetDataReader
        /// <summary>
        /// Provides a way of reading a forward-only stream of rows from a database
        /// </summary>
        /// <param name="commandText">The text of the query</param>
        public DbDataReader GetDataReader(string sqlCommand)
        {
            return GetDataReader(sqlCommand, 0);
        }

		/// <summary>
		/// Provides a way of reading a forward-only stream of rows from a database
        /// </summary>
        /// <param name="sqlCommand">The text of the query</param>
        /// <param name="paramenters">The parameters to bind the query</param>
		public DbDataReader GetDataReader(string sqlCommand, params object[] paramenters)
		{
            DbCommand command = GetParameterizedCommand(sqlCommand, paramenters);
			command.Connection.Open();

            try
            {
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch
            {
                command.Connection.Close();
				throw;
            }
		}
		#endregion

		#region GetDataSet
		/// <summary>
		/// Initializes a new instance of the System.Data.DataSet class.
		/// </summary>
		/// <param name="commandText">The text of the query.</param>
		public DataSet GetDataSet(string commandText)
		{
			return GetDataSet(commandText, CommandType.Text);
		}

		/// <summary>
		/// Initializes a new instance of the System.Data.DataSet class.
		/// </summary>
		/// <param name="commandText">The text of the query.</param>
		/// <param name="commandType">The command type</param>
		public DataSet GetDataSet(string commandText, CommandType commandType)
		{
			DbCommand command = GetCommand(commandText, commandType);
			return GetDataSet(command);
		}

		/// <summary>
		/// Initializes a new instance of the System.Data.DataSet class.
		/// </summary>
		/// <param name="commandText">The select command.</param>
		public DataSet GetDataSet(DbCommand selectCommand)
		{
			DbDataAdapter dataAdapter = this.adapter.Factory.CreateDataAdapter();
			dataAdapter.SelectCommand = selectCommand;
			DataSet dataSet = new DataSet();

			dataAdapter.Fill(dataSet);
			dataAdapter.Dispose();
			selectCommand.Dispose();
			return dataSet;
		}
		#endregion

		#region GetDataTable
		/// <summary>
		/// Returns a new instance of a DataTable
		/// </summary>
		/// <param name="commandText">The text of the query.</param>
		public DataTable GetDataTable(string commandText)
		{
			return GetDataTable(commandText, CommandType.Text);
		}

		/// <summary>
		/// Returns a new instance of a DataTable
		/// </summary>
		/// <param name="commandText">The text of the query.</param>
		/// <param name="commandType">The command type</param>
		public DataTable GetDataTable(string commandText, CommandType commandType)
		{
			DbCommand command = GetCommand(commandText, commandType);
			return GetDataTable(command);
		}

		/// <summary>
		/// Returns a new instance of a DataTable
		/// </summary>
		/// <param name="selectCommand">The select Command</param>
		public DataTable GetDataTable(DbCommand selectCommand)
		{
			DbDataAdapter dataAdapter = adapter.Factory.CreateDataAdapter();
			dataAdapter.SelectCommand = selectCommand;
			DataTable dataTable = new DataTable();

			dataAdapter.Fill(dataTable);
			dataAdapter.Dispose();
			return dataTable;
		}
		#endregion

		#region ExecuteCommand
		
		/// <summary>
		/// Runs a Command
		/// </summary>
		/// <param name="sqlCommand">The text of the query.</param>
		public int ExecuteCommand(string sqlCommand)
		{
			return ExecuteCommand(sqlCommand, new object[0]);
		}

		/// <summary>
		/// Runs a Command
		/// </summary>
		/// <param name="sqlCommand">The text of the query.</param>
		/// <param name="paramenters">The parameter to bind the query</param>
		public int ExecuteCommand(string sqlCommand, object parameter1)
		{
			return ExecuteCommand(sqlCommand, new object[] { parameter1 });
		}

		/// <summary>
		/// Runs a Command
		/// </summary>
		/// <param name="sqlCommand">The text of the query.</param>
		/// <param name="paramenter1">The first parameter to bind the query</param>
		/// <param name="paramenter2">The second parameter to bind the query</param>
		public int ExecuteCommand(string sqlCommand, object parameter1, object parameter2)
		{
			return ExecuteCommand(sqlCommand, new object[] { parameter1, parameter2 });
		}

		/// <summary>
		/// Runs a Command
		/// </summary>
		/// <param name="sqlCommand">The text of the query.</param>
		/// <param name="paramenter1">The first parameter to bind the query</param>
		/// <param name="paramenter2">The second parameter to bind the query</param>
		/// <param name="paramenter2">The third parameter to bind the query</param>
		public int ExecuteCommand(string sqlCommand, object parameter1, object parameter2, object parameter3)
		{
			return ExecuteCommand(sqlCommand, new object[] { parameter1, parameter2, parameter3 });
		}
		
		/// <summary>
		/// Runs a Command
		/// </summary>
		/// <param name="sqlCommand">The text of the query.</param>
		/// <param name="parameters">The parameters of the query</param>
		public int ExecuteCommand(string sqlCommand, object[] parameters)
		{
			return ExecuteCommand(GetParameterizedCommand(sqlCommand, parameters));
		}

		/// <summary>
		/// Runs a Command
		/// </summary>
		/// <param name="commandType">The command object to execute</param>
		public int ExecuteCommand(DbCommand command)
		{
			DbConnection connection = GetConnection();
			try
			{
				connection.Open();
				command.Connection = connection;
				return command.ExecuteNonQuery();
			}
			catch
			{
				connection.Close();
				throw;
			}
		}
		#endregion

		#region GetCommand

		/// <summary>
		/// Returns a safe Command
		/// </summary>
		public DbCommand GetCommand()
		{
			return GetCommand("", CommandType.Text);
		}

		/// <summary>
		/// Returns a safe Text Command
		/// </summary>
		/// <param name="commandText">The text of the query.</param>
		public DbCommand GetCommand(string commandText)
		{
			return GetCommand(commandText, CommandType.Text);
		}

		/// <summary>
		/// Returns a safe Command
		/// </summary>
		/// <param name="commandText">The text of the query.</param>
		/// <param name="commandType">The command type</param>
		public DbCommand GetCommand(string commandText, CommandType commandType)
		{
			DbConnection connection = GetConnection();

			DbCommand command = connection.CreateCommand();
			command.CommandText = commandText;
			command.CommandType = commandType;
			
			return command;
		}
		#endregion
		
		#region GetPrameter
		
		/// <summary>
		/// Returns a new instance of a System.Data.Common.DbParameter object.
		/// </summary>
		/// <param name="name">The parameter name</param>
		/// <param name="value">The parameter value</param>
		/// <returns></returns>
		public DbParameter GetParameter(string name, object value)
		{
			DbParameter parameter = GetParameter();
			parameter.ParameterName = name;
			parameter.Value = value;
			return parameter;
		}

		/// <summary>
		/// Returns a new instance of a System.Data.Common.DbParameter object.
		/// </summary>
		/// <returns></returns>
		public DbParameter GetParameter()
		{
			DbParameter parameter = adapter.Factory.CreateParameter();
			return parameter;
		}
		#endregion

        #region AddParameter
		/// <summary>
		/// Ads a parameter to a command
		/// </summary>
		/// <param name="command">The target command</param>
		/// <param name="name">The parameter name</param>
		/// <param name="value">The parameter value</param>
        public void AddParameter(DbCommand command, string name, object value)
		{
			command.Parameters.Add(GetParameter(name, value));
		}
		#endregion

		#region GetScalar
		/// <summary>
		/// Retrivies a scalar object from data base
		/// </summary>
        /// <param name="sqlCommand">The text of query.</param>
		public object GetScalar(string sqlCommand)
		{
            return GetScalar(sqlCommand, 0);
		}

		/// <summary>
		/// Retrivies a scalar object from data base
		/// </summary>
        /// <param name="sqlCommand">The text of query.</param>
		/// <param name="column">The column index</param>
        public object GetScalar(string sqlCommand, int column)
		{
			return GetScalar(sqlCommand, column, new object[0]);
		}

        /// <summary>
        /// Retrivies a scalar object from data base
        /// </summary>
        /// <param name="sqlCommand">The text of query.</param>
        /// <param name="column">The column index</param>
        /// <param name="paramenter">The parameter to bind the query</param>
        public object GetScalar(string sqlCommand, int column, object parameter)
        {
            return GetScalar(sqlCommand, column, new object[] { parameter });
        }

        /// <summary>
        /// Retrivies a scalar object from data base
        /// </summary>
        /// <param name="sqlCommand">The text of query.</param>
        /// <param name="column">The column index</param>
        /// <param name="paramenter1">The first parameter to bind the query</param>
        /// <param name="paramenter2">The second parameter to bind the query</param>
        public object GetScalar(string sqlCommand, int column, object parameter1, object parameter2)
        {
            return GetScalar(sqlCommand, column, new object[] { parameter1, parameter2 });
        }

        /// <summary>
        /// Retrivies a scalar object from data base
        /// </summary>
        /// <param name="sqlCommand">The text of query.</param>
        /// <param name="column">The column index</param>
        /// <param name="paramenter1">The first parameter to bind the query</param>
        /// <param name="paramenter2">The second parameter to bind the query</param>
        /// <param name="paramenter2">The third parameter to bind the query</param>
        public object GetScalar(string sqlCommand, int column, object parameter1, object parameter2, object parameter3)
        {
            return GetScalar(sqlCommand, column, new object[] { parameter1, parameter2, parameter3 });
        }

		/// <summary>
		/// Retrivies a scalar object from data base
		/// </summary>
        /// <param name="sqlCommand">The text of query.</param>
        /// <param name="column">The column index</param>
        /// <param name="paramenters">The parameters to bind the query</param>
        public object GetScalar(string sqlCommand, int column, object[] parameters)
		{
			IDataReader dataReader = GetDataReader(sqlCommand, 0, parameters);
			object returnObj = null;

			try
			{
				if (dataReader.Read())
				{
					returnObj = dataReader[column];
				}
			}
			finally
			{
				dataReader.Close();
			}
			return returnObj;
		}
		#endregion

		#region Properties

		/// <summary>
		/// The internal database Factory
		/// </summary>
		/*private DbProviderFactory factory
		{
			get
			{
				if (this._factory == null)
				{
					string provider = ConfigurationManager.ConnectionStrings[this.CurrentDataBase].ProviderName;
					this._factory = DbProviderFactories.GetFactory(provider);
				}
				return this._factory;
			}
		}
		private string CurrentDataBase
		{
			get
			{
				if (this._dataBase != null)
				{
					return this._dataBase;
				}
				else
				{
					return Configuration.GetString("DataBase");
				}
			}
		}
		/// <summary>
		/// The current database connection
		/// </summary>
		public DbConnection Connection
		{
			get
			{
				if (this._connection == null)
				{
					string connectionString = ConfigurationManager.ConnectionStrings[this.CurrentDataBase].ConnectionString;

					this._connection = this.factory.CreateConnection();
					this._connection.ConnectionString = connectionString;
				}
				return this._connection;
			}
		}*/

        public DbConnection GetConnection()
        {
			return this.adapter.CreateConnection();
        }
		#endregion

        #region AuxiliarMethods
        private DbCommand GetParameterizedCommand(string sqlCommand, object[] parameters)
        {
            DbCommand output = GetCommand(sqlCommand);

            try
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    int currentIndex = sqlCommand.IndexOf('?');
                    sqlCommand = sqlCommand.Remove(currentIndex, 1).Insert(currentIndex, "@V" + i);
                    AddParameter(output, "@V" + i, parameters[i]);
                }
                output.CommandText = sqlCommand;
            }
            catch { throw new Exception("The parameters length not matches command"); }

            return output;
        }
        #endregion
    }
}